// uecgi_database.cpp
#include <windows.h>
#include <tchar.h>
#include <io.h>
#include <stdio.h>
#include <direct.h>

// UELogger Support
#include "litelog.h"
extern "C" extern int logfd;

#include "base64.h"

// class declaration
#include "uecgi_database.h"
#include "uecgi.h"

#define UE_CGI_DB_NAME          "uecgi.db"
#define UE_CGI_DB_FOLDER        "data\\"

CUECGIDatabase::CUECGIDatabase()
{
	m_uedb.pconnection = NULL;
	m_uedb.type = DEFAULT_UEDB_TYPE;
}

CUECGIDatabase::~CUECGIDatabase()
{
	Close();
}

BOOL CUECGIDatabase::Create(_TCHAR *strBaseDir, BOOL bDropExist)
{
	static char dbspec[] = "DROP TABLE IF EXISTS sessions;\0"
		"CREATE TABLE sessions(create_time integer, session_id varchar(256), token integer, authentication varchar(2048), heartbeat integer);\0";
	BOOL bResult = TRUE;
	uedb_connection uedb;
	char dbconnection_tmpl[] = "type=SQLITE3&path=%s&dbname=%s";
	char dbfolderpath[MAX_PATH + 1];
	char dbfullpath[MAX_PATH + 1];
	int nRet;

	dbglog("Enter CUECGIDatabase::Create()", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__);
	
	// check the input base dir
#if defined(UNICODE) || defined(_UNICODE)
	if (!WideCharToMultiByte(CP_ACP, 0, strBaseDir, -1, dbfolderpath, MAX_PATH + 1, NULL, NULL))
	{
		dbglog("Translate the input unicode base dir into multi byte string failed", DEBUG_LOG_LEVEL_ERRO, logfd, __FILE__, __LINE__);
		bResult = FALSE;
	}
#else
	if (strcpy_s(dbfolderpath, MAX_PATH + 1, strBaseDir))
	{
		dbglog("The input base dir is too long", DEBUG_LOG_LEVEL_ERRO, logfd, __FILE__, __LINE__);
		bResult = FALSE;
	}
#endif

	if (bResult)
	{
		if (!strcat_s(dbfolderpath, MAX_PATH + 1, UE_CGI_DB_FOLDER))
		{
			dbglogex("Get full path of database folder success: %s", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__, dbfolderpath);

			// create the folder if not exist
			if (_access(dbfolderpath, 0))
			{
				_mkdir(dbfolderpath);
			}

			// check if db already exist
			strcpy_s(dbfullpath, MAX_PATH + 1, dbfolderpath);
			if (!strcat_s(dbfullpath, MAX_PATH + 1, UE_CGI_DB_NAME))
			{
				dbglogex("Get full path of database success: %s", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__, dbfullpath);

				if (_access(dbfullpath, 0) == 0)
				{
					dbglog("Database is already exist", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__);
					if (bDropExist)
					{
						dbglog("Try to drop the old database", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__);
						if (remove(dbfullpath) == 0)
						{
							dbglog("Drop the old database success", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__);
						} // drop exist database success
						else
						{
							dbglogex("Drop the old database failed. Error Code: %d", DEBUG_LOG_LEVEL_ERRO, logfd, __FILE__, __LINE__, errno);
							bResult = FALSE;
						} // drop exist database failed
					} // drop exist database
					else
					{
						dbglogex("Database %s is already exist but not drop it", DEBUG_LOG_LEVEL_ERRO, logfd, __FILE__, __LINE__, dbfullpath);
						bResult = FALSE;
					} // don't drop exist database
				} // db is already exist
			} // get db full path success
			else
			{
				dbglogex("Get full path of database failed, possibly the db folder path is too long: %s", DEBUG_LOG_LEVEL_ERRO, logfd, __FILE__, __LINE__, dbfolderpath);
				bResult = FALSE;
			} // get db full path failed

			if (bResult)
			{
				// connect to database
				int len = strlen(dbconnection_tmpl) + strlen(dbfolderpath) + strlen(UE_CGI_DB_NAME) + 1;
				char *dbconnection = (char*)malloc(len);
				if (dbconnection)
				{
					dbglog("Malloc memory for database connection string success.", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__);

					sprintf_s(dbconnection, len, dbconnection_tmpl, dbfolderpath, UE_CGI_DB_NAME);
					dbglogex("Connection string: %s.", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__, dbconnection);

					uedb.pconnection = NULL;
					uedb.type = DEFAULT_UEDB_TYPE;

					nRet = uedb_connect(dbconnection, &uedb);
					if (!nRet)
					{
						dbglog("Connect to database success", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__);

						// create database
						nRet = uedb_create(dbspec, &uedb);
						if (!nRet)
						{
							dbglog("Create database success", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__);
						} // create database success
						else
						{
							dbglogex("Create database failed with return value: %d", DEBUG_LOG_LEVEL_ERRO, logfd, __FILE__, __LINE__, nRet);
							bResult = FALSE;
						} // create database failed

						// close database connection
						uedb_close(&uedb);
					} // connect to database success
					else
					{
						dbglogex("Connect to database failed with return value: %d", DEBUG_LOG_LEVEL_ERRO, logfd, __FILE__, __LINE__, nRet);
						bResult = FALSE;
					} // connect to database failed

					free(dbconnection);
				} // malloc memory for dbconnection string success
				else
				{
					dbglog("Malloc memory for database connection string failed.", DEBUG_LOG_LEVEL_ERRO, logfd, __FILE__, __LINE__);
					bResult = FALSE;
				} // malloc memory for dbconnection string failed
			} // check if database already exist success
		} // get full path of database folder success
		else
		{
			dbglogex("Get full path of database folder failed, possibly the input basedir is too long: %s", DEBUG_LOG_LEVEL_ERRO, logfd, __FILE__, __LINE__, dbfolderpath);
			bResult = FALSE;
		} // get full path of database folder failed
	} // input basedir check success

	dbglog("Exit CUECGIDatabase::Create", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__);

	return bResult;
}

BOOL CUECGIDatabase::Connect()
{
	BOOL bResult = TRUE;
	_TCHAR strBaseDir[MAX_PATH + 1];
	char dbfolderpath[MAX_PATH + 1];
	char dbconnection_tmpl[] = "type=SQLITE3&path=%s&dbname=%s";
	int nRet;

	dbglog("Enter CUECGIDatabase::Connect()", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__);

	// close old connection if needed
	Close();

	// get cgi database folder
	if (GetModuleFileName(NULL, strBaseDir, MAX_PATH))
	{
		// get base dir firstly
		dbglog("Get module file path success", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__);
		_TCHAR* pos = _tcsrchr(strBaseDir, _T('\\'));
		if (pos)
		{
			// {basedir}www/cgi-bin
			pos[0] = 0;
			pos = _tcsrchr(strBaseDir, _T('\\'));        
			if (pos)
			{
				// {basedir}www
				pos[0] = 0;
				pos = _tcsrchr(strBaseDir, _T('\\'));  
				if (pos)
				{
					pos[1] = 0;
				}
			}
		}

		// if needed, translate the basedir from unicode to ansi
#if defined(UNICODE) || defined(_UNICODE)
		if (!WideCharToMultiByte(CP_ACP, 0, strBaseDir, -1, dbfolderpath, MAX_PATH + 1, NULL, NULL))
		{
			dbglog("Translate the unicode base dir into multi byte string failed", DEBUG_LOG_LEVEL_ERRO, logfd, __FILE__, __LINE__);
			bResult = FALSE;
		}
#else
		if (strcpy_s(dbfolderpath, MAX_PATH + 1, strBaseDir))
		{
			dbglog("The base dir is too long", DEBUG_LOG_LEVEL_ERRO, logfd, __FILE__, __LINE__);
			bResult = FALSE;
		}
#endif
		if (bResult)
		{
			dbglogex("Get basedir success: %s", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__, dbfolderpath);

			if (!strcat_s(dbfolderpath, MAX_PATH + 1, UE_CGI_DB_FOLDER))
			{
				dbglogex("Get full path of database folder success: %s", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__, dbfolderpath);

				// connect to database
				int len = strlen(dbconnection_tmpl) + strlen(dbfolderpath) + strlen(UE_CGI_DB_NAME) + 1;
				char *dbconnection = (char*)malloc(len);
				if (dbconnection)
				{
					dbglog("Malloc memory for database connection string success.", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__);

					sprintf_s(dbconnection, len, dbconnection_tmpl, dbfolderpath, UE_CGI_DB_NAME);
					dbglogex("Connection string: %s.", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__, dbconnection);

					nRet = uedb_connect(dbconnection, &m_uedb);
					if (!nRet)
					{
						dbglog("Connect to database success", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__);
					} // connect to database success
					else
					{
						dbglogex("Connect to database failed with return value: %d", DEBUG_LOG_LEVEL_ERRO, logfd, __FILE__, __LINE__, nRet);
						bResult = FALSE;
					} // connect to database failed

					free(dbconnection);
				} // malloc memory for dbconnection string success
				else
				{
					dbglog("Malloc memory for database connection string failed.", DEBUG_LOG_LEVEL_ERRO, logfd, __FILE__, __LINE__);
					bResult = FALSE;
				} // malloc memory for dbconnection string failed
			} // get full path of database folder success
			else
			{
				dbglogex("Get full path of database folder failed, possibly the basedir is too long: %s", DEBUG_LOG_LEVEL_ERRO, logfd, __FILE__, __LINE__, dbfolderpath);
				bResult = FALSE;
			} // get full path of database folder failed
		} // get basedir success
	} // get module file path success
	else
	{
		dbglogex("Get module file path failed. Error Code: %d", DEBUG_LOG_LEVEL_ERRO, logfd, __FILE__, __LINE__, GetLastError());
		bResult = FALSE;
	} // get module file path failed
	
	dbglog("Exit CUECGIDatabase::Connect", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__);

	return bResult;
}

BOOL CUECGIDatabase::Execute(const char *strSQL)
{
	BOOL bResult = TRUE;
	int nRet;

	dbglogex("Enter CUECGIDatabase::Execute(%s)", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__, strSQL);

	// parameter check
	if (strSQL && strlen(strSQL) > 0)
	{
		// check if we have valid connection
		if (m_uedb.pconnection)
		{
			dbglog("Begin execute SQL statement...", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__);

			// execute SQL statement
			nRet = uedb_execute(strSQL, &m_uedb);
			if (!nRet)
			{
				dbglogex("Execute SQL statement success", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__);
			} // execute SQL success
			else
			{
				dbglogex("Execute SQL statement failed with return value: %d", DEBUG_LOG_LEVEL_ERRO, logfd, __FILE__, __LINE__, nRet);
				bResult = FALSE;
			} // execute SQL failed
		} // connection is already established
		else
		{
			dbglog("No connection established", DEBUG_LOG_LEVEL_ERRO, logfd, __FILE__, __LINE__);
			bResult = FALSE;
		} // no connection established
	} // parameter check success
	else
	{
		dbglog("Invalid parameter, the input SQL is empty", DEBUG_LOG_LEVEL_ERRO, logfd, __FILE__, __LINE__);
		bResult = FALSE;
	} // parameter check failed

	dbglog("Exit CUECGIDatabase::Execute", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__);

	return bResult;
}

BOOL CUECGIDatabase::ExecuteEx(const char *strSQL, const uedb_parameter* pParams, const unsigned int nParams)
{
	BOOL bResult = TRUE;
	int nRet;

	dbglogex("Enter CUECGIDatabase::ExecuteEx(%s, %x, %d)", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__, strSQL, pParams, nParams);

	// parameter check
	if (strSQL && strlen(strSQL) > 0 && (nParams == 0 || nParams > 0 && pParams))
	{
		// check if we have valid connection
		if (m_uedb.pconnection)
		{
			dbglog("Begin execute SQL statement...", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__);

			// execute SQL statement
			nRet = uedb_execute_ex(strSQL, pParams, nParams, &m_uedb);
			if (!nRet)
			{
				dbglogex("Execute SQL statement success", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__);
			} // execute SQL success
			else
			{
				dbglogex("Execute SQL statement failed with return value: %d", DEBUG_LOG_LEVEL_ERRO, logfd, __FILE__, __LINE__, nRet);
				bResult = FALSE;
			} // execute SQL failed
		} // connection is already established
		else
		{
			dbglog("No connection established", DEBUG_LOG_LEVEL_ERRO, logfd, __FILE__, __LINE__);
			bResult = FALSE;
		} // no connection established
	} // parameter check success
	else
	{
		dbglog("Invalid parameter, the input SQL is empty or parameters invalid", DEBUG_LOG_LEVEL_ERRO, logfd, __FILE__, __LINE__);
		bResult = FALSE;
	} // parameter check failed

	dbglog("Exit CUECGIDatabase::ExecuteEx", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__);

	return bResult;
}

BOOL CUECGIDatabase::Query(const char *strSQL)
{
	BOOL bResult = TRUE;
	int nRet;

	dbglogex("Enter CUECGIDatabase::Query(%s)", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__, strSQL);

	// parameter check
	if (strSQL && strlen(strSQL) > 0)
	{
		// check if we have valid connection
		if (m_uedb.pconnection)
		{
			dbglog("Begin execute SQL query...", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__);

			// close previous query if any
			CloseQuery();

			// execute SQL query
			nRet = uedb_query(strSQL, &m_uedb);
			if (!nRet)
			{
				dbglogex("Execute SQL query success", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__);
			} // execute SQL success
			else
			{
				dbglogex("Execute SQL query failed with return value: %d", DEBUG_LOG_LEVEL_ERRO, logfd, __FILE__, __LINE__, nRet);
				CloseQuery();
				bResult = FALSE;
			} // execute SQL failed
		} // connection is already established
		else
		{
			dbglog("No connection established", DEBUG_LOG_LEVEL_ERRO, logfd, __FILE__, __LINE__);
			bResult = FALSE;
		} // no connection established
	} // parameter check success
	else
	{
		dbglog("Invalid parameter, the input SQL is empty", DEBUG_LOG_LEVEL_ERRO, logfd, __FILE__, __LINE__);
		bResult = FALSE;
	} // parameter check failed

	dbglog("Exit CUECGIDatabase::Query", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__);

	return bResult;
}

void CUECGIDatabase::Close()
{
	dbglog("Enter CUECGIDatabase::Close()", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__);

	if (m_uedb.pconnection)
	{
		dbglogex("Has connection %x established, close it", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__, m_uedb.pconnection);

		uedb_close(&m_uedb);
		m_uedb.pconnection = NULL;
	}

	dbglog("Exit CUECGIDatabase::Close", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__);
}

int CUECGIDatabase::GetRows()
{
	int nResult = 0;
	int nRet;

	dbglog("Enter CUECGIDatabase::GetRows()", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__);

	// check if we have valid connection
	if (m_uedb.pconnection)
	{
		dbglog("Begin get row number in last query result", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__);

		// execute SQL query
		nRet = uedb_rows(&m_uedb, &nResult);
		if (!nRet)
		{
			dbglogex("Get row number success", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__);
		} // execute SQL success
		else
		{
			dbglogex("Get row number failed with return value: %d", DEBUG_LOG_LEVEL_ERRO, logfd, __FILE__, __LINE__, nRet);
		} // execute SQL failed
	} // connection is already established
	else
	{
		dbglog("No connection established", DEBUG_LOG_LEVEL_ERRO, logfd, __FILE__, __LINE__);
	} // no connection established

	dbglog("Exit CUECGIDatabase::GetRows", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__);

	return nResult;
}

const char *CUECGIDatabase::GetField(const char *strColumnName, const int nRowNum)
{
	const char* strResult = NULL;
	int nRet;

	dbglogex("Enter CUECGIDatabase::GetFields(%s, %d)", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__, strColumnName, nRowNum);

	if (strColumnName && strlen(strColumnName) > 0 && nRowNum >= 0)
	{
		dbglog("Input column name or row number is valid", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__);

		// get result
		nRet = uedb_result_by_name(strColumnName, nRowNum, &m_uedb, &strResult);
		if (!nRet)
		{
			dbglogex("Get result success", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__);
		} // get result success
		else
		{
			dbglogex("Get result failed with return value: %d", DEBUG_LOG_LEVEL_ERRO, logfd, __FILE__, __LINE__, nRet);
		} // get result failed
	} // parameter check success
	else
	{
		dbglog("Input column name or row number invalid", DEBUG_LOG_LEVEL_WARN, logfd, __FILE__, __LINE__);
	} // parameter check failed

	dbglog("Exit CUECGIDatabase::GetRows", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__);

	return strResult;
}

const char *CUECGIDatabase::GetField(const int nColumnNum, const int nRowNum)
{
	const char* strResult = NULL;
	int nRet;

	dbglogex("Enter CUECGIDatabase::GetFields(%d, %d)", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__, nColumnNum, nRowNum);

	if (nColumnNum >= 0 && nRowNum >= 0)
	{
		dbglog("Input column number or row number is valid", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__);

		// get result
		nRet = uedb_result_by_index(nColumnNum, nRowNum, &m_uedb, &strResult);
		if (!nRet)
		{
			dbglogex("Get result success", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__);
		} // get result success
		else
		{
			dbglogex("Get result failed with return value: %d", DEBUG_LOG_LEVEL_ERRO, logfd, __FILE__, __LINE__, nRet);
		} // get result failed
	} // parameter check success
	else
	{
		dbglog("Input column name or row number invalid", DEBUG_LOG_LEVEL_WARN, logfd, __FILE__, __LINE__);
	} // parameter check failed

	dbglog("Exit CUECGIDatabase::GetRows", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__);

	return strResult;
}

BOOL CUECGIDatabase::GetAuthentication(const char* strSessionID, const int nCSRFToken, char* buf, const unsigned int nLen)
{
	BOOL bResult = TRUE;
	const char *result = NULL;

	dbglogex("Enter CUECGIDatabase::GetAuthentication(%s, %d, %x, %d)", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__, strSessionID, nCSRFToken, buf, nLen);

	// parameter check
	if (strSessionID && strlen(strSessionID) > 0 && buf)
	{
		char SQL[MAX_SQL_LEN];
		sprintf_s(SQL, MAX_SQL_LEN, "select authentication from sessions where session_id = '%s' and token = %d order by create_time desc limit 1", strSessionID, nCSRFToken);
		if (Query(SQL))
		{
			dbglog("Execute SQL query success", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__);
			if (GetRows() > 0)
			{
				result = GetField(0, 0);
				if (result && strlen(result) < nLen)
				{
					strcpy_s(buf, nLen, result);
				}
				else
				{
					dbglogex("Get result failed or result is too long, %s", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__, result);
					bResult = FALSE;
				}
			} // find session info
			else
			{
				dbglog("Can't find related session info", DEBUG_LOG_LEVEL_ERRO, logfd, __FILE__, __LINE__);
				bResult = FALSE;
			} // not find session info
		} // query data success
		else
		{
			dbglogex("Execute SQL query failed: %s", DEBUG_LOG_LEVEL_ERRO, logfd, __FILE__, __LINE__, SQL);
			bResult = FALSE;
		} // query data failed
	} // parameter check success
	else
	{
		dbglog("Invalid parameter, the session id is empty or output buf is null", DEBUG_LOG_LEVEL_ERRO, logfd, __FILE__, __LINE__);
		bResult = FALSE;
	} // parameter check failed

	dbglog("Exit CUECGIDatabase::GetAuthentication", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__);

	return bResult;
}

void CUECGIDatabase::CloseQuery()
{
	int nRet;

	dbglogex("Enter CUECGIDatabase::CloseQuery()", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__);

	// check if we have valid connection
	if (m_uedb.pconnection)
	{
		dbglog("Begin close last query result", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__);

		// execute SQL query
		nRet = uedb_close_query(&m_uedb);
		if (!nRet)
		{
			dbglogex("Close query success", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__);
		} // execute SQL success
		else
		{
			dbglogex("Close query failed with return value: %d", DEBUG_LOG_LEVEL_ERRO, logfd, __FILE__, __LINE__, nRet);
		} // execute SQL failed
	} // connection is already established
	else
	{
		dbglog("No connection established to close query, nothing to do.", DEBUG_LOG_LEVEL_WARN, logfd, __FILE__, __LINE__);
	} // no connection established

	dbglog("Exit CUECGIDatabase::CloseQuery", DEBUG_LOG_LEVEL_INFO, logfd, __FILE__, __LINE__);
}